package com.nice.controller;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Collections;
import java.util.LinkedList;
import java.util.List;

import org.apache.log4j.Logger;

import com.nice.main.Bootstart;
import com.nice.message.Message;
import com.nice.service.Service;

public class H2Dao implements MessageDao,Service {

	private static Logger log = Logger.getLogger(H2Dao.class);
	private static final String TABLE_SQL = "CREATE table msg("
			+ "id long,_type int,_len int,_from int,_to int,_body varchar(253));";
	private Connection conn;


	private void init(boolean reCreatetable) {
		try {
			Class.forName("org.h2.Driver");
			conn = DriverManager.getConnection("jdbc:h2:~/msg.db");
			Statement st = conn.createStatement();
			DatabaseMetaData metaData = conn.getMetaData();
			if (reCreatetable) {
				st.execute("drop table if exists msg;");
			} else {
				ResultSet rs = metaData.getTables(null, null, "MSG", null);
				if (rs.next()) {
					log.info("table msg exist");
					rs.close();
					st.close();
					return;
				}
				rs.close();
				st.close();
			}
			st.execute(TABLE_SQL);
			st.close();
			log.info("connect db2 ok,table is ok");
		} catch (Exception e) {
			e.printStackTrace();
		}
	}


	@Override
	public boolean save(Message msg) {
		try {
			PreparedStatement st = conn
					.prepareStatement("insert into msg values(?,?,?,?,?,?)");
			st.setLong(1, msg.id);
			st.setInt(2, msg.type);
			st.setInt(3, msg.len);
			st.setInt(4, msg.from);
			st.setInt(5, msg.to);
			st.setString(6, new String(msg.body));
			boolean res = st.execute();
			st.close();
			log.debug("save msg ok");
			return res;
		} catch (SQLException e) {
			log.error("save msg eror", e);
		}
		return false;
	}

	@Override
	public List<Message> query(int clientId) {
		try {
			List<Message> msgs = new LinkedList<Message>();
			PreparedStatement st = conn
					.prepareStatement("select * from msg where _to=?");
			st.setInt(1, clientId);
			ResultSet rs = st.executeQuery();
			while (rs.next()) {
				Message mg = new Message();
				mg.id = rs.getLong(1);
				mg.type = rs.getByte(2);
				mg.len = rs.getInt(3);
				mg.from = rs.getInt(4);
				mg.to = rs.getInt(5);
				mg.body = rs.getString(6).getBytes();
				msgs.add(mg);
			}
			st.close();
			return msgs;
		} catch (SQLException e) {
			log.error("query msg eror", e);
		}
		return Collections.emptyList();
	}

	@Override
	public boolean delete(List<Message> msgs) {
		try {
			if (msgs.isEmpty())
				return false;
			Statement st = conn.createStatement();
			String ids = "";
			int i = 0;
			int size = msgs.size() - 1;
			for (; i < size; i++) {
				ids += msgs.get(i).id + ",";
			}
			ids += msgs.get(i).id;
			String sql = "delete from msg where id in(" + ids + ");";
			System.out.println(sql);
			boolean res = st.execute(sql);
			st.close();
			return res;
		} catch (SQLException e) {
			log.error("delete msg eror", e);
		}
		return false;
	}


	@Override
	public void start(Bootstart bt) {
		init(false);
	}

	@Override
	public void stop(Bootstart bt) {
		try {
			conn.close();
		} catch (SQLException e) {
			log.error("close conn  eror", e);
		}
	}
}
